# -*- coding: utf-8 -*-

# Neote -- A note taking application
#
# Copyright (C) 2009 Valéry Febvre <vfebvre@easter-eggs.com>
# http://code.google.com/p/neote/
#
# This file is part of Neote.
#
# Neote is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as
# published by the Free Software Foundation, either version 3 of the
# License, or (at your option) any later version.
#
# Neote is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import os, time
import sqlite3

db = None

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

class NeoteDB(object):
    def __init__(self):
        db_dir = os.path.join(os.path.expanduser("~"), ".config/neote/")
        if not os.path.exists(db_dir):
            os.makedirs(db_dir)
        db_path = os.path.join(db_dir, "neote.db")

        self.cnx = sqlite3.connect(db_path)
        self.cnx.row_factory = dict_factory

        cursor = self.cnx.cursor()
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS notes (
                id         INTEGER PRIMARY KEY,
                kind       INTEGER,
                data       TEXT,
                comment    TEXT,
                created    REAL,
                updated    REAL
            );
            """)
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS categories (
                id      INTEGER PRIMARY KEY,
                name    TEXT
            );
            """)
        cursor.executescript("""
            CREATE TABLE IF NOT EXISTS notes_categories (
                note_id        INTEGER,
                category_id    INTEGER
            );
            """)

        # create default categories if not exist
        if cursor.execute('SELECT count(*) AS nb FROM categories').fetchone()['nb'] == 0:
            for category in ['Personal', 'Work', 'Important', 'To Do', 'Later', 'Special']:
                self.add_category(category)

    def add_note(self, kind, data):
        cursor = self.cnx.cursor()

        sql = """
            INSERT INTO notes (kind, data, created, updated) VALUES (?, ?, ?, ?)
        """
        cursor.execute(sql, (kind, unicode(data, 'utf-8'), time.time(), time.time()))
        self.cnx.commit()

        return cursor.lastrowid

    def add_category(self, name):
        cursor = self.cnx.cursor()

        sql = """
            INSERT INTO categories (name) VALUES (?)
        """
        cursor.execute(sql, (unicode(name, 'utf-8'),))
        self.cnx.commit()

        return cursor.lastrowid

    def add_note_category(self, note_id, category_id):
        cursor = self.cnx.cursor()

        sql = """
            INSERT INTO notes_categories (note_id, category_id) VALUES (?, ?)
        """
        cursor.execute(sql, (note_id, category_id))
        self.cnx.commit()

    def delete_category(self, id):
        cursor = self.cnx.cursor()

        sql = """
            DELETE FROM notes_categories WHERE category_id = ?
        """
        cursor.execute(sql, (id,))

        sql = """
            DELETE FROM categories WHERE id = ?
        """
        cursor.execute(sql, (id,))

        self.cnx.commit()

    def delete_note(self, id):
        cursor = self.cnx.cursor()

        sql = """
            DELETE FROM notes WHERE id = ?
        """
        cursor.execute(sql, (id,))

        sql = """
            DELETE FROM notes_categories WHERE note_id = ?
        """
        cursor.execute(sql, (id,))

        self.cnx.commit()

    def delete_note_category(self, note_id, category_id):
        cursor = self.cnx.cursor()

        sql = """
            DELETE FROM notes_categories WHERE note_id = ? AND category_id = ?
        """
        cursor.execute(sql, (note_id, category_id))
        self.cnx.commit()

    def get_category(self, id = None, name = None):
        if id:
            sql = """
                SELECT * FROM categories WHERE id = ?
            """
            return self.cnx.cursor().execute(sql, (id,)).fetchone()
        elif name:
            sql = """
                SELECT * FROM categories WHERE name = ?
            """
            return self.cnx.cursor().execute(sql, (unicode(name, 'utf-8'),)).fetchone()

    def get_note(self, id):
        sql = """
            SELECT * FROM notes WHERE id = ?
        """
        return self.cnx.cursor().execute(sql, (id,)).fetchone()

    def get_note_categories(self, note_id):
        sql = """
            SELECT * FROM notes_categories WHERE note_id = ?
        """
        return self.cnx.cursor().execute(sql, (note_id,)).fetchall()

    def get_notes(self, category_id = None):
        if not category_id:
            sql = """
                SELECT * FROM notes ORDER BY updated DESC
            """
            return self.cnx.cursor().execute(sql).fetchall()
        else:
            if category_id == 'uncategorized':
                sql = """
                    SELECT * FROM notes
                    WHERE id NOT IN (SELECT note_id FROM notes_categories)
                    ORDER BY updated DESC
                """
                return self.cnx.cursor().execute(sql).fetchall()
            else:
                sql = """
                    SELECT * FROM notes, notes_categories
                    WHERE notes.id = notes_categories.note_id
                    AND notes_categories.category_id = ?
                    ORDER BY updated DESC
                """
                return self.cnx.cursor().execute(sql, (category_id,)).fetchall()

    def get_categories(self):
        sql = """
            SELECT * FROM categories ORDER BY name ASC
        """
        return self.cnx.cursor().execute(sql).fetchall()

    def search_notes(self, words):
        sql = """
            SELECT notes.* FROM notes
            LEFT JOIN notes_categories ON notes.id = notes_categories.note_id
            LEFT JOIN categories ON notes_categories.category_id = categories.id
            WHERE notes.data LIKE ? OR categories.name LIKE ?
            ORDER BY notes.updated DESC
        """
        return self.cnx.cursor().execute(sql, ('%%%s%%' % words, '%%%s%%' % words)).fetchall()

    def update_category(self, id, name):
        sql = """
            UPDATE categories SET name = ? WHERE id = ?
        """
        self.cnx.cursor().execute(sql, (unicode(name, 'utf-8'), id))
        self.cnx.commit()

    def update_note(self, id, data):
        sql = """
            UPDATE notes SET data = ?, updated = ? WHERE id = ?
        """
        self.cnx.cursor().execute(sql, (unicode(data, 'utf-8'), time.time(), id))
        self.cnx.commit()

    def close(self):
        self.cnx.close()

db = NeoteDB()


def get_note_title(note):
    # used first line as title
    return note['data'].split('<br>')[0].encode('utf-8')
